# _*_coding:utf-8_*_
__author__ = 'gerry'
import pandas as pd
import MySQLdb
from pandas.io import sql


file_path = "C:\Users\Gerry\Desktop\data\catering_sale.xls"

def read_data_df():
    catering_sale_df = pd.read_excel(file_path, index_col=u'日期')  # 读取数据，指定日期为索引列


    catering_sale_df = catering_sale_df[(catering_sale_df[u'销量'] > 400) & (catering_sale_df[u'销量'] < 5000)]  # 过滤异常数据
    statistic = catering_sale_df.describe()  # 保存基本统计量
    statistic.loc['range'] = statistic.loc['max']-statistic.loc['min']#极差
    statistic.loc['var'] = statistic['std']/statistic.loc['mean']#变异系数
    statistic.loc['dis'] = statistic.loc['75%']-statistic.loc['25%']#四分位间距
    print(statistic)
    return catering_sale_df


def write_data_MySQL():
    # 获得数据
    Write_data = read_data_df()
    # 获得数据库连接
    conn = MySQLdb.connect(host="127.0.0.1", port=3306, user="root", passwd="123456", db="python",use_unicode=True, charset="utf8")
    cur = conn.cursor()

    cur.execute("Drop TABLE If EXISTS catering_sale")
    pd.io.sql.to_sql(Write_data, "catering_sale",conn,flavor='mysql')

    cur.close()
    conn.close()

if __name__ == '__main__':
    read_data_df()
    write_data_MySQL()
